
package com.lmx.persistence.service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import com.lmx.common.context.ActionContextHolder;
import com.lmx.common.util.CommonUtil;
import com.lmx.repoDomain.tmodel.Dicttype;
import com.lmx.repoDomain.tmodel.StatQuery;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;


/**
 * 
 * 
 * @author huibati
 * @version 1.00
 */
@Service
@Scope("prototype")
public class StatService<T> {

	private Log logger = LogFactory.getLog(this.getClass());

	@PersistenceContext
	private EntityManager em;

	@Autowired
	protected GenericService dslJpaService;

	@Autowired
	protected CommonService commonService;

	@Resource(name = "jdbcTemplate")
	private JdbcTemplate jdbcTemplate;

	protected JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}



	/**
	 * 日查询
	 */
	public List statQueryForDay(String statType, Integer beginTime, Integer endTime) {
		List dataListTemp = new ArrayList<>();
		List dataList= new ArrayList<>();
		try {
			List<Dicttype> statItems = commonService.getStatItemByType(statType);
			Dicttype type = commonService.getDicttype(statType);
			Long tenantId = ActionContextHolder.getContext().getTenantId();
			Map<Long,Dicttype> itemMap=new HashMap<>();
			if (statItems != null && statItems.size() > 0) {
				String items = "( ";
				for (int i = 0; i < statItems.size(); i++) {
					items += statItems.get(i).getId() + ",";
					itemMap.put(statItems.get(i).getId(), statItems.get(i));
				}
				items = items.substring(0, items.length() - 1) + " )";
				StringBuilder sql =new StringBuilder("select * from "
						+ "(SELECT t.RepDate,t.id,t2.del_status,t2.tenant_id,t2.stat_item,t2.stat_type FROM temp_day t,tnb_statquery_querytype t2 WHERE "
						+" t.RepDate >= FROM_UNIXTIME("+beginTime+", '%Y-%m-%d')"
						+" AND t.RepDate <= FROM_UNIXTIME("+endTime+", '%Y-%m-%d')"
						+" AND t2.stat_type = "+ type.getId()
						+" AND t2.stat_item IN "+items +") t3"
						+ " LEFT JOIN "
						+ " (select FROM_UNIXTIME(stat_date, '%Y-%m-%d') as statDate, stat_type,stat_item as statItemId,sum(stat_result) as sumResult,"
						+ " tenant_id as tenantId from tnb_statquery " 
						+ " where  del_status!=1 " 
						+ " and tenant_id =" + tenantId
						+ " and stat_type =" + type.getId() 
						+ " and stat_date >=" + beginTime 
						+ " and stat_date <="+ endTime 
						+ " and stat_item in " + items
						+ " group by FROM_UNIXTIME(stat_date, '%Y-%m-%d') ,stat_type,stat_item,tenant_id "
						+ " order by FROM_UNIXTIME(stat_date, '%Y-%m-%d') ,stat_type,stat_item )t1 "
						+ " on  (t1.statDate= t3.RepDate and t3.stat_item=t1.statItemId and t3.stat_type=t1.stat_type) " );
				dataListTemp = getJdbcTemplate().queryForList(sql.toString());
				Iterator it = dataListTemp.iterator();
				while (it.hasNext()) {
					Map rece = (Map) it.next();
					//System.out.println(rece.get("statDate")+"---"+rece.get("statItemId")+"---"+rece.get("sumResult")+"---"+rece.get("tenantId"));
					StatQuery temp=new StatQuery();
					temp.setStatDate(CommonUtil.strToIntegerTime(rece.get("RepDate")+" 00:00:00"));
					if(rece.get("sumResult")!=null){
						temp.setStatResult(Integer.parseInt(rece.get("sumResult").toString()));
					}
					else{
						temp.setStatResult(0);
					}
					temp.setStatType(type);
					temp.setStatItemType(itemMap.get(rece.get("stat_item")));
					temp.setId((long)CommonUtil.buildRandom(8));
					dataList.add(temp);
				}
			}
		} catch (Exception e) {
			logger.debug( "statQueryForDay error "+e);
		}

		return dataList;
	}

	/**
	 * 月查询
	 */
	public List statQueryForMonth(String statType, Integer beginTime, Integer endTime) {
		List dataListTemp = new ArrayList<>();
		List dataList= new ArrayList<>();
		try {
			List<Dicttype> statItems = commonService.getStatItemByType(statType);
			Dicttype type = commonService.getDicttype(statType);
			Long tenantId = ActionContextHolder.getContext().getTenantId();
			Map<Long,Dicttype> itemMap=new HashMap<>();
			if (statItems != null && statItems.size() > 0) {
				String items = "( ";
				for (int i = 0; i < statItems.size(); i++) {
					items += statItems.get(i).getId() + ",";
					itemMap.put(statItems.get(i).getId(), statItems.get(i));
				}
				items = items.substring(0, items.length() - 1) + " )";
				StringBuilder sql =new StringBuilder( "select * from "
						+ "(SELECT t.RepDate,t.id,t2.del_status,t2.tenant_id,t2.stat_item,t2.stat_type FROM temp_month t,tnb_statquery_querytype t2 WHERE "
						+" t.RepDate >= FROM_UNIXTIME("+beginTime+", '%Y-%m-%d')"
						+" AND t.RepDate <= FROM_UNIXTIME("+endTime+", '%Y-%m-%d')"
						+" AND t2.stat_type = "+ type.getId()
						+" AND t2.stat_item IN "+items +") t3"
						+" LEFT JOIN "
						+ " (select substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,7) as statDate, stat_type,stat_item as statItemId,sum(stat_result) as sumResult,"
						+ " tenant_id as tenantId from tnb_statquery " 
						+ " where  del_status!=1 " 
						+ " and tenant_id =" + tenantId
						+ " and stat_type =" + type.getId() 
						+ " and stat_date >=" + beginTime 
						+ " and stat_date <="+ endTime 
						+ " and stat_item in " + items
						+ " group by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,7) ,stat_type,stat_item,tenant_id "
						+ " order by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,7) ,stat_type,stat_item )t1 "
						+ " on  (t1.statDate= substring(t3.RepDate,1,7) and t3.stat_item=t1.statItemId and t3.stat_type=t1.stat_type) ");
				dataListTemp = getJdbcTemplate().queryForList(sql.toString());
				Iterator it = dataListTemp.iterator();
				while (it.hasNext()) {
					Map rece = (Map) it.next();
					System.out.println(rece.get("statDate")+"---"+rece.get("statItemId")+"---"+rece.get("sumResult")+"---"+rece.get("tenantId"));
					StatQuery temp=new StatQuery();
					temp.setStatDate(CommonUtil.strToIntegerTime(rece.get("RepDate")+" 00:00:00"));
					if(rece.get("sumResult")!=null){
						temp.setStatResult(Integer.parseInt(rece.get("sumResult").toString()));
					}
					else{
						temp.setStatResult(0);
					}
					temp.setStatType(type);
					temp.setStatItemType(itemMap.get(rece.get("stat_item")));
					temp.setId((long)CommonUtil.buildRandom(8));
					dataList.add(temp);
				}
			}
		} catch (Exception e) {
			logger.debug( "statQueryForMonth error "+e);
		}

		return dataList;
	}

	/**
	 * 季度查询 quarter(FROM_UNIXTIME(stat_date, '%Y-%m-%d'))
	 */
	public List statQueryForQuarter(String statType, Integer beginTime, Integer endTime) {
		List dataListTemp = new ArrayList<>();
		List dataList= new ArrayList<>();
		try {
			List<Dicttype> statItems = commonService.getStatItemByType(statType);
			Dicttype type = commonService.getDicttype(statType);
			Long tenantId = ActionContextHolder.getContext().getTenantId();
			Map<Long,Dicttype> itemMap=new HashMap<>();
			if (statItems != null && statItems.size() > 0) {
				String items = "( ";
				for (int i = 0; i < statItems.size(); i++) {
					items += statItems.get(i).getId() + ",";
					itemMap.put(statItems.get(i).getId(), statItems.get(i));
				}
				items = items.substring(0, items.length() - 1) + " )";
				StringBuilder sql =new StringBuilder( "select * from "
						+ "(SELECT t.RepDate,t.id,t.start_time,t.end_time,t2.del_status,t2.tenant_id,t2.stat_item,t2.stat_type FROM temp_quarter t,tnb_statquery_querytype t2 WHERE "
						+" t.start_time >= FROM_UNIXTIME("+beginTime+", '%Y-%m-%d')"
						+" AND t.end_time <= FROM_UNIXTIME("+endTime+", '%Y-%m-%d')"
						+" AND t2.stat_type = "+ type.getId()
						+" AND t2.stat_item IN "+items +") t3"
						+ " LEFT JOIN "
						+ " (select substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) as statDateYear,quarter(FROM_UNIXTIME(stat_date, '%Y-%m-%d')) as statDateQ,stat_type,stat_item as statItemId,sum(stat_result) as sumResult,"
						+ " tenant_id as tenantId from tnb_statquery " 
						+ " where  del_status!=1 " 
						+ " and tenant_id =" + tenantId
						+ " and stat_type =" + type.getId() 
						+ " and stat_date >="+ beginTime 
						+ " and stat_date <="+ endTime 
						+ " and stat_item in " + items
						+ " group by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) ,quarter(FROM_UNIXTIME(stat_date, '%Y-%m-%d')) ,stat_type,stat_item,tenant_id"
						+ " order by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) ,quarter(FROM_UNIXTIME(stat_date, '%Y-%m-%d')) ,stat_type,stat_item) t1 "
						+ " on  (CONCAT(t1.statDateYear,'-',t1.statDateQ)= substring(t3.RepDate,1,6)  and t3.stat_item=t1.statItemId and t3.stat_type=t1.stat_type) ");
				dataListTemp = getJdbcTemplate().queryForList(sql.toString());
				Iterator it = dataListTemp.iterator();
				while (it.hasNext()) {
					Map rece = (Map) it.next();
					System.out.println(rece.get("statDateYear")+"-"+rece.get("statDateQ")+"---"+rece.get("statItemId")+"---"+rece.get("sumResult")+"---"+rece.get("tenantId"));
					StatQuery temp=new StatQuery();

					temp.setStatDate(CommonUtil.strToIntegerTime(rece.get("start_time")+" 00:00:00"));
					if(rece.get("sumResult")!=null){
						temp.setStatResult(Integer.parseInt(rece.get("sumResult").toString()));
					}
					else{
						temp.setStatResult(0);
					}
					temp.setStatType(type);
					temp.setStatItemType(itemMap.get(rece.get("stat_item")));
					temp.setId((long)CommonUtil.buildRandom(8));
					dataList.add(temp);
				}
			}
		} catch (Exception e) {
			logger.debug( "statQueryForQuarter error "+e);
		}
		return dataList;
	}

	/**
	 * 年查询
	 */
	public List statQueryForYear(String statType, Integer beginTime, Integer endTime) {
		List dataListTemp = new ArrayList<>();
		List dataList= new ArrayList<>();
		try {
			List<Dicttype> statItems = commonService.getStatItemByType(statType);
			Dicttype type = commonService.getDicttype(statType);
			Long tenantId = ActionContextHolder.getContext().getTenantId();
			Map<Long,Dicttype> itemMap=new HashMap<>();
			if (statItems != null && statItems.size() > 0) {
				String items = "( ";
				for (int i = 0; i < statItems.size(); i++) {
					items += statItems.get(i).getId() + ",";
					itemMap.put(statItems.get(i).getId(), statItems.get(i));
				}
				items = items.substring(0, items.length() - 1) + " )";
				StringBuilder sql =new StringBuilder("select * from "
						+ "(SELECT t.RepDate,t.id,t2.del_status,t2.tenant_id,t2.stat_item,t2.stat_type FROM temp_year t,tnb_statquery_querytype t2 WHERE "
						+" t.RepDate >= FROM_UNIXTIME("+beginTime+", '%Y-%m-%d')"
						+" AND t.RepDate <= FROM_UNIXTIME("+endTime+", '%Y-%m-%d')"
						+" AND t2.stat_type = "+ type.getId()
						+" AND t2.stat_item IN "+items +") t3"
						+ " LEFT JOIN "
						+ " (select substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) as statDate,stat_type,stat_item as statItemId,sum(stat_result) as sumResult, "
						+ " tenant_id as tenantId from tnb_statquery " 
						+ " where  del_status!=1 " 
						+ " and tenant_id =" + tenantId
						+ " and stat_type =" + type.getId() 
						+ " and stat_date >="+ beginTime 
						+ " and stat_date <="+ endTime 
						+ " and stat_item in " + items
						+ " group by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) ,stat_type,stat_item,tenant_id"
						+ " order by substring(FROM_UNIXTIME(stat_date, '%Y-%m-%d'),1,4) ,stat_type,stat_item) t1 "
						+ " on  (t1.statDate= substring(t3.RepDate,1,4) and t3.stat_item=t1.statItemId and t3.stat_type=t1.stat_type) ");
				dataListTemp = getJdbcTemplate().queryForList(sql.toString());
				Iterator it = dataListTemp.iterator();
				while (it.hasNext()) {
					Map rece = (Map) it.next();
					System.out.println(rece.get("statDate")+"---"+rece.get("statItemId")+"---"+rece.get("sumResult")+"---"+rece.get("tenantId"));
					StatQuery temp=new StatQuery();
					temp.setId(Long.parseLong(rece.get("id").toString()));
					temp.setStatDate(CommonUtil.strToIntegerTime(rece.get("RepDate")+" 00:00:00"));
					if(rece.get("sumResult")!=null){
						temp.setStatResult(Integer.parseInt(rece.get("sumResult").toString()));
					}
					else{
						temp.setStatResult(0);
					}
					temp.setStatType(type);
					temp.setStatItemType(itemMap.get(rece.get("stat_item")));
					temp.setId((long)CommonUtil.buildRandom(8));
					dataList.add(temp);
				}
			}
		} catch (Exception e) {
			logger.debug( "statQueryForYear error "+e);
		}
		return dataList;
	}

}
